How to Add WITH (NOLOCK) and Handle Parameter Sniffing in Entity Framework

WARNING

The implementation approach in this article may be considered an Anti-Pattern in some modern scenarios. For more recommended alternatives (such as the RCSI architecture and TagWith implementation), please refer to the latest comprehensive discussion: Introduction to RCSI and Improved Entity Framework Locking Hint Interceptor

I was asked about SQL Server's WITH (NOLOCK) during an interview a few days ago. Because I hadn't used it in a long time, I couldn't recall it immediately, which led to an incorrect answer. It turns out the information was in my notes from a year ago: "SQL Server Performance Tuning".

Why is WITH (NOLOCK) important in SQL Server, yet I haven't used it in a long time? The main reason is that most development today uses Entity Framework directly, rather than manually crafting SQL statements like in the past. We can simply let the library modify the SQL before executing the command.

Now, let's look at how to implement the same behavior in Entity Framework.

Interceptor

The Interceptor in Microsoft.EntityFrameworkCore was introduced in version 3.0, while it was added to the .NET Framework's EntityFramework in version 6.0. Its primary function is to allow modification or interception of operations while Entity Framework is executing low-level database operations or SaveChanges(). For more specific details, please refer to MSDN's "Interceptors". This article uses the Microsoft.EntityFrameworkCore version as an example.

Interceptor Interfaces

• IDbCommandInterceptor: Handles methods related to DbCommand. This article will use this interface.
• IDbConnectionInterceptor: Handles methods related to opening and closing connections.
• IDbTransactionInterceptor: Handles methods related to transactions.
• ISaveChangesInterceptor: Handles methods related to SaveChanges().

Implementation Method

Regarding the handling of WITH (NOLOCK), most solutions found online cannot handle subqueries. However, the article "Add With NoLock to EF Core Queries" provides a more advanced approach, so I referenced it for my modifications.

public class FixDbCommandInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;
    private static readonly Regex cudRegex = new(@"\b(INSERT|UPDATE|DELETE)\b", regexOptions);
    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(FROM|JOIN)\s+\[\w+\]\s+AS\s+\[\w+\])",
        regexOptions
    );

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(
       DbCommand command, CommandEventData eventData,
       InterceptionResult<DbDataReader> result
    ) {
        FixCommand(command);
        return base.ScalarExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<object> result, CancellationToken cancellationToken = default
    ) {
        FixCommand(command);
        return await base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    private static void FixCommand(IDbCommand command) {
        string commandText = command.CommandText;

        // Some mutation scenarios, such as querying before mutating, might cause EF to call ExecuteReader instead of ExecuteNonQuery
        // So we need to exclude these cases
        if (cudRegex.IsMatch(commandText)) {
            return;
        }

        // If Single or First is called, it might be for precise data retrieval (e.g., fetching data to mutate), so NOLOCK should not be added
        if (!commandText.Contains("TOP(1)") && !commandText.Contains("TOP(2)")) {
            commandText = tableAliasRegex.Replace(commandText, "${tableAlias} WITH (NOLOCK)");
        }

        // Although EF-generated Select statements do not end with a semicolon, mutation statements do. 
        // To be safe, we still need to handle it.
        commandText = commandText.TrimEnd(';') + " OPTION (OPTIMIZE FOR UNKNOWN);";

        command.CommandText = commandText;
    }
}

Code Explanation

1. DbCommandInterceptor has already implemented all methods of IDbCommandInterceptor, so you only need to inherit from it and override the required methods.
2. Methods related to queries include ExecuteReader() and ExecuteScalar(), so we override the corresponding pre-execution methods of IDbCommandInterceptor for both synchronous and asynchronous versions.
3. CommandText modification:
   • Some mutation syntax that returns values might use ExecuteScalar(), so we skip INSERT, UPDATE, and DELETE statements.
   • WITH (NOLOCK) is intended to prevent blocking when data is locked, but it is inappropriate if you are retrieving data to perform a mutation. Therefore, we skip syntax containing TOP(1) (e.g., First() or Find()) and TOP(2) (e.g., Single()).
   • Added OPTION (OPTIMIZE FOR UNKNOWN); to handle Parameter Sniffing.

WARNING

The above processing lacks verification in actual production environments; please adjust it according to your specific needs.

Adding the Interceptor

You can add the Interceptor using the following two methods:

• Add the following code in your DbContext:

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.AddInterceptors(new FixDbCommandInterceptor());

• Configure it from DbContextOptionsBuilder when injecting DbContext into DI:

services.AddDbContext<TestDbContext>(options => {
    options
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new FixDbCommandInterceptor());
});

Actual Execution Results

Use the following SQL to create the table and use reverse engineering to build the EF models:

CREATE TABLE [dbo].[Test](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestInt] [int] NOT NULL,
    [TestBit] [bit] NOT NULL,
    [TestDateTime] [datetime2](7) NOT NULL,
    [TestGuid] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED (
 [Id] ASC
) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SubTest](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestId] [int] NOT NULL,
    CONSTRAINT [PK_SubTest] PRIMARY KEY CLUSTERED (
 [Id] ASC
) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SubTest]  WITH CHECK ADD  CONSTRAINT [FK_SubTest_Test] FOREIGN KEY([TestId])
REFERENCES [dbo].[Test] ([Id])
GO

Execute the following code:

context.Tests.Find(1);

context.Tests
    .Include(x => x.SubTests)
    .SingleOrDefault(x => x.Id == 1);

context.Tests
    .Include(x => x.SubTests)
    .ToList();

The generated SQL syntax is as follows:

-- Find()
SELECT TOP(1) [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt]
FROM [Test] AS [t]
WHERE [t].[Id] = @__p_0 OPTION (OPTIMIZE FOR UNKNOWN);

-- SingleOrDefault()
SELECT [t0].[Id], [t0].[TestBit], [t0].[TestDateTime], [t0].[TestGuid], [t0].[TestInt], [s].[Id], [s].[TestId]
FROM (
    SELECT TOP(2) [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt]
    FROM [Test] AS [t]
    WHERE [t].[Id] = 1
) AS [t0]
LEFT JOIN [SubTest] AS [s] ON [t0].[Id] = [s].[TestId]
ORDER BY [t0].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

-- ToList()
SELECT [t].[Id], [t].[TestBit], [t].[TestDateTime], [t].[TestGuid], [t].[TestInt], [s].[Id], [s].[TestId]
FROM [Test] AS [t] WITH (NOLOCK)
LEFT JOIN [SubTest] AS [s] WITH (NOLOCK) ON [t].[Id] = [s].[TestId]
ORDER BY [t].[Id] OPTION (OPTIMIZE FOR UNKNOWN);

Revision History

• 2024-07-18 Initial version created.